package introspect

import (
	"database/sql"
	"fmt"
	"strings"

	"pgm/internal/database"
)

// TableInfo 表信息
type TableInfo struct {
	Name        string       `json:"name"`
	Schema      string       `json:"schema"`
	Comment     string       `json:"comment"`
	Columns     []ColumnInfo `json:"columns"`
	PrimaryKeys []string     `json:"primary_keys"`
	ForeignKeys []ForeignKey `json:"foreign_keys"`
}

// ColumnInfo 列信息
type ColumnInfo struct {
	Name         string `json:"name"`
	DataType     string `json:"data_type"`
	IsNullable   bool   `json:"is_nullable"`
	DefaultValue string `json:"default_value"`
	Comment      string `json:"comment"`
	MaxLength    int    `json:"max_length"`
	Position     int    `json:"position"`
}

// ForeignKey 外键信息
type ForeignKey struct {
	ColumnName     string `json:"column_name"`
	RefTableName   string `json:"ref_table_name"`
	RefColumnName  string `json:"ref_column_name"`
	ConstraintName string `json:"constraint_name"`
}

// Inspector 数据库内省器
type Inspector struct {
	db *sql.DB
}

// NewInspector 创建新的内省器
func NewInspector() *Inspector {
	return &Inspector{db: database.DB}
}

// GetTables 获取所有表信息
func (i *Inspector) GetTables(schema string) ([]TableInfo, error) {
	if schema == "" {
		schema = "public"
	}

	query := `
		SELECT 
			t.table_name,
			t.table_schema,
			COALESCE(obj_description(c.oid), '') as table_comment
		FROM information_schema.tables t
		LEFT JOIN pg_class c ON c.relname = t.table_name
		LEFT JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema
		WHERE t.table_schema = $1 AND t.table_type = 'BASE TABLE'
		ORDER BY t.table_name
	`

	rows, err := i.db.Query(query, schema)
	if err != nil {
		return nil, fmt.Errorf("获取表列表失败: %w", err)
	}
	defer rows.Close()

	var tables []TableInfo
	for rows.Next() {
		var table TableInfo
		err := rows.Scan(&table.Name, &table.Schema, &table.Comment)
		if err != nil {
			return nil, fmt.Errorf("扫描表信息失败: %w", err)
		}

		// 获取列信息
		columns, err := i.GetColumns(table.Name, schema)
		if err != nil {
			return nil, fmt.Errorf("获取表 %s 的列信息失败: %w", table.Name, err)
		}
		table.Columns = columns

		// 获取主键信息
		primaryKeys, err := i.GetPrimaryKeys(table.Name, schema)
		if err != nil {
			return nil, fmt.Errorf("获取表 %s 的主键信息失败: %w", table.Name, err)
		}
		table.PrimaryKeys = primaryKeys

		// 获取外键信息
		foreignKeys, err := i.GetForeignKeys(table.Name, schema)
		if err != nil {
			return nil, fmt.Errorf("获取表 %s 的外键信息失败: %w", table.Name, err)
		}
		table.ForeignKeys = foreignKeys

		tables = append(tables, table)
	}

	return tables, nil
}

// GetColumns 获取表的列信息
func (i *Inspector) GetColumns(tableName, schema string) ([]ColumnInfo, error) {
	query := `
		SELECT 
			c.column_name,
			c.data_type,
			CASE WHEN c.is_nullable = 'YES' THEN true ELSE false END as is_nullable,
			COALESCE(c.column_default, '') as default_value,
			COALESCE(c.character_maximum_length, 0) as max_length,
			c.ordinal_position,
			COALESCE(col_description(pgc.oid, c.ordinal_position), '') as comment
		FROM information_schema.columns c
		LEFT JOIN pg_class pgc ON pgc.relname = c.table_name
		LEFT JOIN pg_namespace pgn ON pgn.oid = pgc.relnamespace AND pgn.nspname = c.table_schema
		WHERE c.table_name = $1 AND c.table_schema = $2
		ORDER BY c.ordinal_position
	`

	rows, err := i.db.Query(query, tableName, schema)
	if err != nil {
		return nil, fmt.Errorf("获取列信息失败: %w", err)
	}
	defer rows.Close()

	var columns []ColumnInfo
	for rows.Next() {
		var col ColumnInfo
		err := rows.Scan(
			&col.Name,
			&col.DataType,
			&col.IsNullable,
			&col.DefaultValue,
			&col.MaxLength,
			&col.Position,
			&col.Comment,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描列信息失败: %w", err)
		}
		columns = append(columns, col)
	}

	return columns, nil
}

// GetPrimaryKeys 获取表的主键信息
func (i *Inspector) GetPrimaryKeys(tableName, schema string) ([]string, error) {
	query := `
		SELECT kcu.column_name
		FROM information_schema.table_constraints tc
		JOIN information_schema.key_column_usage kcu 
			ON tc.constraint_name = kcu.constraint_name 
			AND tc.table_schema = kcu.table_schema
		WHERE tc.constraint_type = 'PRIMARY KEY' 
			AND tc.table_name = $1 
			AND tc.table_schema = $2
		ORDER BY kcu.ordinal_position
	`

	rows, err := i.db.Query(query, tableName, schema)
	if err != nil {
		return nil, fmt.Errorf("获取主键信息失败: %w", err)
	}
	defer rows.Close()

	var primaryKeys []string
	for rows.Next() {
		var columnName string
		if err := rows.Scan(&columnName); err != nil {
			return nil, fmt.Errorf("扫描主键信息失败: %w", err)
		}
		primaryKeys = append(primaryKeys, columnName)
	}

	return primaryKeys, nil
}

// GetForeignKeys 获取表的外键信息
func (i *Inspector) GetForeignKeys(tableName, schema string) ([]ForeignKey, error) {
	query := `
		SELECT 
			kcu.column_name,
			ccu.table_name as foreign_table_name,
			ccu.column_name as foreign_column_name,
			tc.constraint_name
		FROM information_schema.table_constraints tc
		JOIN information_schema.key_column_usage kcu 
			ON tc.constraint_name = kcu.constraint_name
			AND tc.table_schema = kcu.table_schema
		JOIN information_schema.constraint_column_usage ccu 
			ON ccu.constraint_name = tc.constraint_name
			AND ccu.table_schema = tc.table_schema
		WHERE tc.constraint_type = 'FOREIGN KEY' 
			AND tc.table_name = $1 
			AND tc.table_schema = $2
	`

	rows, err := i.db.Query(query, tableName, schema)
	if err != nil {
		return nil, fmt.Errorf("获取外键信息失败: %w", err)
	}
	defer rows.Close()

	var foreignKeys []ForeignKey
	for rows.Next() {
		var fk ForeignKey
		err := rows.Scan(
			&fk.ColumnName,
			&fk.RefTableName,
			&fk.RefColumnName,
			&fk.ConstraintName,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描外键信息失败: %w", err)
		}
		foreignKeys = append(foreignKeys, fk)
	}

	return foreignKeys, nil
}

// GetTableByName 根据名称获取单个表信息
func (i *Inspector) GetTableByName(tableName, schema string) (*TableInfo, error) {
	if schema == "" {
		schema = "public"
	}

	tables, err := i.GetTables(schema)
	if err != nil {
		return nil, err
	}

	for _, table := range tables {
		if strings.EqualFold(table.Name, tableName) {
			return &table, nil
		}
	}

	return nil, fmt.Errorf("表 %s 不存在", tableName)
}
